package com.larry.motion.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Marshaller;
import javax.xml.bind.Unmarshaller;

import com.larry.motion.vo.RegionType;
import com.pegatron.motion.stub.Add;
import com.pegatron.motion.stub.CustomFieldValue;
import com.pegatron.motion.stub.CustomFieldValues;
import com.pegatron.motion.stub.CustomerHandle;
import com.pegatron.motion.stub.Estimate;
import com.pegatron.motion.stub.ItemLine;
import com.pegatron.motion.stub.ItemList;
import com.pegatron.motion.stub.ItemRef;
import com.pegatron.motion.stub.Name;
import com.pegatron.motion.stub.Request;
import com.pegatron.motion.stub.SalesPrice;
import com.pegatron.motion.stub.Smbxml;
import com.pegatron.motion.stub.StatusRef;

public class MotionQSService extends MotionService {

	public MotionQSService(String region) {
		super(region);
		this.region = region;
	}

	@Override
	public String genFileName(Integer index) throws Exception {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
		String dateStr = sdf.format(new Date());
		String key = RegionType.valueOf(region).getFileKey();
		new File(folder + dateStr).mkdirs();
		return folder + dateStr + "/" + "R02QS" + key + "MTC" + dateStr + "." + index + ".xml";
	}

	@Override
	public Boolean genMessageFile(String fileName, String orderNr) throws Exception {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			JAXBContext jc = JAXBContext.newInstance(STUB_CONTEXT_PATH);
			Marshaller m = jc.createMarshaller();
			m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
			m.setProperty(Marshaller.JAXB_FRAGMENT, Boolean.TRUE);
			conn = super.getConnection();
			StringBuffer buf = this.getSQL(orderNr);
			System.out.println(buf.toString());
			statement = conn.prepareStatement(buf.toString());
			rs = statement.executeQuery();
			int i = 0;
			while (rs.next()) {
				i++;
				File file = new File(fileName);
				System.out.println("marshalQS[" + i + "], fileName=" + fileName + ", file.length()=" + file.length());
				FileWriter fw;
				if (file.length() == 0) {
					Smbxml smb = new Smbxml();
					smb.setMissingFieldOverwrite("false");
					smb = this.processMessage(smb, "QS", rs);

					fw = new FileWriter(file, true);
					fw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
					fw.write("<!DOCTYPE smbxml SYSTEM \"http://www.netsuite.com/xml/dtd/smb_5_01.dtd\">\n");
					m.marshal(smb, fw);
					fw.close();
				} else {
					Unmarshaller u = jc.createUnmarshaller();
					Smbxml smb = (Smbxml) u.unmarshal(new FileInputStream(fileName));
					smb = this.processMessage(smb, "QS", rs);

					fw = new FileWriter(file, false);
					fw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
					fw.write("<!DOCTYPE smbxml SYSTEM \"http://www.netsuite.com/xml/dtd/smb_5_01.dtd\">\n");
					m.marshal(smb, fw);
					fw.close();
				}
			}
			return true;
		} catch (IOException e) {
			e.printStackTrace();
		} catch (JAXBException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			super.closeConnection(conn, statement, rs);
		}
		return false;
	}

	@Override
	public StringBuffer getSQL(String orderNr) throws Exception {
		StringBuffer buf = new StringBuffer();
		buf.append("	SELECT");
		buf.append("		a.order_no,");
		buf.append("		c.item_no, ");
		buf.append("		a.rma_site, ");
		buf.append("		a.order_nr, ");
		buf.append("		a.order_type, ");
		buf.append("		d.customer_po case_number, ");
		buf.append("		d.customer_pn case_handle, ");
		buf.append("		b.mrb case_entity_handle, ");
		buf.append("		c.serial_nr, ");
		buf.append("		TO_CHAR((SELECT x.issue_date FROM ers_item_onhold x WHERE x.order_no=c.order_no AND x.item_no=c.item_no AND x.rma_site=c.rma_site AND ROWNUM=1), 'MM/DD/YYYY') issue_date");
		buf.append("	FROM");
		buf.append("		ers_order a,");
		buf.append("		ers_order_hist b,");
		buf.append("		ers_item c,");
		buf.append("		ers_order_extra d");
		buf.append(" WHERE");
		buf.append("		a.dt_id IS NULL");
		buf.append("		and c.dt_id IS NULL ");
		buf.append("		and a.order_no=b.order_no ");
		buf.append("		and a.rma_site=b.rma_site ");
		buf.append("		and a.order_no=c.order_no ");
		buf.append("		and a.rma_site=c.rma_site ");
		buf.append("		and a.order_no=d.order_no ");
		buf.append("		and a.rma_site=d.rma_site ");
		buf.append("		and a.rma_site IN (62, 64) ");
		buf.append("		and EXISTS(SELECT 1 FROM ers_order_track x WHERE x.order_no=a.order_no AND x.rma_site=a.rma_site AND x.status='ACCEPT' AND x.memo='MOTION_EDI_AUTO_CREATE')");
		buf.append("		and a.order_nr in ('" + orderNr + "')");
		buf.append("		and a.order_type NOT IN ('W', 'RP', 'FM') ");
		return buf;
	}
	
	public Smbxml processMessage(Smbxml smb, String key, ResultSet rs) throws Exception {
		Request request = new Request();
		request.setMissingFieldOverwrite("true");
		Add add = new Add();
		Estimate estimate = new Estimate();
		StatusRef statusRef = new StatusRef();
		Name name = new Name();
		name.setvalue("Open - New Quote Needs Review");
		statusRef.setName(name);
		estimate.setStatusRef(statusRef);

		estimate.setTranId(rs.getString("case_number"));

		CustomerHandle customerHandle = new CustomerHandle();
		customerHandle.setHandle(rs.getString("case_entity_handle"));
		estimate.getCustomerHandleOrCustomerRef().add(customerHandle);

		estimate.setTranDate(rs.getString("issue_date"));

		CustomFieldValues customFieldValues = new CustomFieldValues();
		CustomFieldValue customFieldValue = new CustomFieldValue();
		customFieldValue.setName("Serial Number");
		customFieldValue.setvalue(rs.getString("serial_nr"));
		customFieldValues.getCustomFieldValue().add(customFieldValue);

		estimate.setCustomFieldValues(customFieldValues);

		ItemList itemList = new ItemList();

		Connection conn1 = null;
		PreparedStatement statement1 = null;
		ResultSet rs1 = null;
		try {
			conn1 = getConnection();
			StringBuffer buf = new StringBuffer();
			buf.append("	SELECT");
			buf.append("		m.case_handle,");
			buf.append("		m.case_number,");
			buf.append("		m.case_entity_handle,");
			buf.append("		m.order_no,");
			buf.append("		m.item_no,");
			buf.append("		m.rma_site,");
			buf.append("		m.status,");
			buf.append("		m.order_type,");
			buf.append("		m.part_nr,");
			buf.append("		m.part_desc,");
			buf.append("		m.serial_nr,");
			buf.append("		m.reason_code,");
			buf.append("		(SELECT x.reason_name FROM ers_cf_reason x WHERE x.reason_id=m.reason_code) reason,");
			buf.append("		m.problem_code,");
			buf.append("		(rma_util_p.GETPROBLEMDESC(m.order_no, m.item_NO, m.RMA_site, TO_NUMBER (m.code_group), m.PROBLEM_CODE)) problem,");
			buf.append("		m.code_group,");
			buf.append("		m.qty,");
			buf.append("		m.total_price,");
			buf.append("		m.material_price,");
			buf.append("		m.labor_price,");
			buf.append("		m.customer,");
			buf.append("		m.keyin_date,");
			buf.append("		m.warranty,");
			buf.append("		m.guid,");
			buf.append("		m.finish,");
			buf.append("		m.response_date,");
			buf.append("		m.response_result");
			buf.append("	FROM");
			buf.append("		(SELECT");
			buf.append("			d.case_handle,");
			buf.append("			d.case_number, ");
			buf.append("			d.case_entity_handle, ");
			buf.append("			b.order_no, ");
			buf.append("			b.item_no, ");
			buf.append("			b.rma_site, ");
			buf.append("			b.process_no, ");
			buf.append("			e.status, ");
			buf.append("			g.order_type, ");
			buf.append("			(SELECT x.serial_nr FROM ers_item_track x WHERE x.order_no=b.order_no AND x.item_no=b.item_no AND x.rma_site=b.rma_site AND x.track_no=1) serial_nr,");
			buf.append("			(SELECT x.short_desc FROM wm_part_master x WHERE x.part_no=f.part_nr) part_desc,");
			buf.append("			f.part_nr,");
			buf.append("			(SELECT CODE_GROUP FROM RM_BASE_DEFINE x WHERE x.RMA_CENTER=g.rma_site AND x.PRODUCT_TYPE=h.product_type AND x.RMA_TYPE = g.order_type AND x.CUSTOMER_TYPE=ers_util_p.getcustomertype(g.customer)) code_group,");
			buf.append("			c.problem_code, ");
			buf.append("			c.reason_code, ");
			buf.append("			(SELECT x.keyin_date FROM ers_item_summary x WHERE x.item_no = b.item_no AND x.rma_site=b.rma_site) keyin_date,");
			buf.append("			TO_DATE((SELECT x.custom_svc_center_data02 FROM ers_edi_motion_header x WHERE x.order_no=b.order_no AND x.rma_site=b.rma_site AND ROWNUM=1), 'MM/DD/YYYY') warranty,");
			buf.append("			1 qty, ");
			buf.append("			DECODE(elm_order.getPrice4Motion(f.part_nr), -1, 0, elm_order.getPrice4Motion (f.part_nr))+108.7 total_price,");
			buf.append("			DECODE(elm_order.getPrice4Motion(f.part_nr), -1, 0, elm_order.getPrice4Motion (f.part_nr)) material_price,");
			buf.append("			108.7 labor_price,");
			buf.append("			g.customer,");
			buf.append("			a.guid,");
			buf.append("			a.finish,");
			buf.append("			b.RESPONSE_RESULT,");
			buf.append("			a.response_date");
			buf.append("		FROM");
			buf.append("			ers_item_onhold a,");
			buf.append("			ers_item_onhold_detail b,");
			buf.append("			ers_process c,");
			buf.append("			ers_edi_motion_header d,");
			buf.append("			ers_mtc_order e,");
			buf.append("			ers_mtc_order_item f,");
			buf.append("			ers_order g,");
			buf.append("			ers_item h");
			buf.append("		WHERE");
			buf.append("			a.guid=B.GUID");
			buf.append("			AND a.order_no=b.order_no");
			buf.append("			AND a.item_no=b.item_no");
			buf.append("			AND a.rma_site=b.rma_site");
			buf.append("			AND b.item_no=c.item_no");
			buf.append("			AND b.rma_site=c.rma_site");
			buf.append("			AND b.process_no=c.process_no");
			buf.append("			AND a.order_no=D.ORDER_NO");
			buf.append("			AND a.rma_site=d.rma_site");
			buf.append("			AND b.order_no=E.APPLY_ORDER_NO");
			buf.append("			AND b.item_no=e.apply_item_no");
			buf.append("			AND B.PROCESS_NO=E.APPLY_PROCESS_NO");
			buf.append("			AND E.MTC_ORDER_NO=f.mtc_order_no");
			buf.append("			AND a.order_no=g.order_no");
			buf.append("			AND g.order_no=h.order_no");
			buf.append("			AND g.rma_site=h.rma_site");
			buf.append("			AND h.item_no=a.item_no");
			buf.append("			AND a.order_no=" + rs.getString("order_no"));
			buf.append("			AND a.item_no=" + rs.getString("item_no"));
			buf.append("			AND a.rma_site=" + rs.getString("rma_site"));
			buf.append("			AND a.finish='N') m");
			buf.append("		WHERE m.order_type='R'");
			statement1 = conn1.prepareStatement(buf.toString());
			rs1 = statement1.executeQuery();
			int i = 0;
			while (rs1.next()) {
				i++;
				ItemLine itemLine = new ItemLine();
				ItemRef itemRef = new ItemRef();
				Name name1 = new Name();
				name1.setvalue("CID Part");
				itemRef.setName(name1);
				itemLine.getItemHandleOrItemRef().add(itemRef);
				itemLine.setQuantity(rs1.getString("qty"));
				SalesPrice salesPrice = new SalesPrice();
				salesPrice.setvalue(rs1.getString("total_price"));
				itemLine.getSalesPriceOrAmount().add(salesPrice);
				itemLine.setDescription(rs1.getString("part_desc"));
				CustomFieldValues itemCustomFieldValues = new CustomFieldValues();
				CustomFieldValue itemCustomFieldValue = new CustomFieldValue();
				itemCustomFieldValue.setName("Part Used");
				itemCustomFieldValue.setvalue(rs1.getString("part_nr"));
				itemCustomFieldValues.getCustomFieldValue().add(itemCustomFieldValue);
				itemCustomFieldValue = new CustomFieldValue();
				itemCustomFieldValue.setName("CID Material Price");
				itemCustomFieldValue.setvalue(rs1.getString("material_price"));
				itemCustomFieldValues.getCustomFieldValue().add(itemCustomFieldValue);
				itemCustomFieldValue = new CustomFieldValue();
				itemCustomFieldValue.setName("CID Labor Price");
				if (i == 1) {
					itemCustomFieldValue.setvalue(rs1.getString("labor_price"));
				} else {
					itemCustomFieldValue.setvalue("0");
				}
				itemCustomFieldValues.getCustomFieldValue().add(itemCustomFieldValue);

				itemCustomFieldValue = new CustomFieldValue();
				itemCustomFieldValue.setName("CID Quote Reason");
				itemCustomFieldValue.setvalue(rs1.getString("reason"));
				itemCustomFieldValues.getCustomFieldValue().add(itemCustomFieldValue);

				itemCustomFieldValue = new CustomFieldValue();
				itemCustomFieldValue.setName("ServiceCenterQuoteData01");
				itemCustomFieldValue.setvalue("Quoting unit");
				itemCustomFieldValues.getCustomFieldValue().add(itemCustomFieldValue);

				itemCustomFieldValue = new CustomFieldValue();
				itemCustomFieldValue.setName("ServiceCenterQuoteData02");
				itemCustomFieldValue.setvalue(rs1.getString("problem"));
				itemCustomFieldValues.getCustomFieldValue().add(itemCustomFieldValue);

				itemLine.setCustomFieldValues(itemCustomFieldValues);
				itemList.getItemLine().add(itemLine);
			}
			estimate.setItemList(itemList);
			add.getAccountOrCustomerOrVendorOrEmployeeOrContactOrPartnerOrCashSaleOrSalesOrderOrInvoiceOrEstimateOrCreditMemoOrReturnAuthorizationOrCustomerPaymentOrJournalEntryOrPurchaseOrderOrBillOrInventoryItemOrSalesTaxItemOrNonInventoryResaleItemOrOtherChargeResaleItemOrServiceResaleItemOrNonInventoryPurchItemOrNonInventorySaleItemOrOtherChargePurchItemOrOtherChargeSaleItemOrServicePurchItemOrServiceSaleItemOrDiscountItemOrMarkupItemOrSubtotalItemOrDescriptionItemOrMediaItemOrTopicOrSolutionOrCaseOrEventOrTaskOrCustomFieldOrCustomListOrCustomRecordTypeOrCustomRecordOrOtherListOrNoteOrOpportunityOrTimecardOrCustomerCategoryOrVendorCategoryOrJobTypeOrPaymentMethodOrStoreCategoryOrMessage()
					.add(estimate);
			request.getAddOrAddOrUpdateOrQueryOrGet().add(add);
			smb.getHeaderOrRequestOrResponse().add(request);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn1, statement1, rs1);
		}
		return smb;
	}
}
